package com.zx.dao.impl;

import com.zx.bean.Courier;
import com.zx.dao.CourierDao;
import com.zx.util.DruidUtil;

import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * @Author CaesarChang张旭
 * @Date 2021/4/22  3:44 下午
 * @Version 1.0
 */
public class CourierDaoImpl implements CourierDao {
    //定义SQL
    private static final String SQL_INSERT="insert into Courier (username,password,sendNum,userphone,idCard,register_time) " +
            "values(?,?,?,?,?,now())";

    private static final String SQL_UPDATE="update Courier  set username=?,password=?,idCard=?,userphone=? where id=?";

    private static final String SQL_FIND_ALL = "select * from Courier";

    private static final String SQL_DELETE = "delete from Courier where id=?";

    private static final String SQL_FIND_LIMIT = "select * from Courier limit ?,?";

    private static final String SQL_FIND_BY_USERPHONE = "select * from Courier where userphone=?";
    private static final String SQL_FIND_BY_USERNAME = "select * from Courier where username=?";
    private static final String SQL_UPDATE_LATEST_TIME = "update Courier set latest_time=?  where username=?";
    private static final String SQL_TOTAL = "select count(id) total from Courier";
    private static final String SQL_CONSOLE = "select count(id) data_size, count(to_days(register_time)=to_days(now()) or null) data1_day from Courier";







    @Override
    public Map<String, Integer> getConsole() {
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        Map<String,Integer> data=new HashMap<>();
        try {
            connection = DruidUtil.getConnection();
            preparedStatement = connection.prepareStatement(SQL_CONSOLE);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int data_size = resultSet.getInt("data_size");
                int data1_day = resultSet.getInt("data1_day");
                data.put("data_size",data_size);
                data.put("data_day",data1_day);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return data;
    }


    @Override
    public boolean insert(Courier courier) {
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
             preparedStatement = connection.prepareStatement(SQL_INSERT);
             preparedStatement.setString(1,courier.getUsername());
             preparedStatement.setString(2,courier.getPassword());
             preparedStatement.setInt(3,courier.getSendNum());
             preparedStatement.setString(4,courier.getUserphone());
             preparedStatement.setString(5,courier.getIdCard());


            return preparedStatement.executeUpdate()>0?true:false;
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return false;
    }

    @Override
    public boolean delete(int id) {
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            preparedStatement = connection.prepareStatement(SQL_DELETE);
            preparedStatement.setInt(1,id);


            return preparedStatement.executeUpdate()>0?true:false;
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }


        return false;
    }

    @Override
    public boolean update(int id, Courier courier) {
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE);
            preparedStatement.setString(1,courier.getUsername());
            preparedStatement.setString(2,courier.getPassword());
            preparedStatement.setString(3,courier.getIdCard());
            preparedStatement.setString(4,courier.getUserphone());
            preparedStatement.setInt(5, id);



            return preparedStatement.executeUpdate()>0?true:false;
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return false;
    }

    @Override
    public boolean update_SendNum(String userphone) {
        return false;
    }

    @Override
    public Courier findByPhone(String userphone) {
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_USERPHONE);
            preparedStatement.setString(1,userphone);

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");

                String password = resultSet.getString("password");
                String username = resultSet.getString("username");
                int sendNum = resultSet.getInt("sendNum");

                String idCard= resultSet.getString("idCard");
                Timestamp register_time = resultSet.getTimestamp("register_time");
                Timestamp latest_time = resultSet.getTimestamp("latest_time");

                Courier courier = new Courier(id, username, password,
                        userphone, sendNum, idCard, register_time, latest_time);

                return courier;
            }


        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return null;
    }

    @Override
    public Courier findByUsername(String username) {
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_USERNAME);
            preparedStatement.setString(1,username);

            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");

                String password = resultSet.getString("password");
                int sendNum = resultSet.getInt("sendNum");
                String userphone= resultSet.getString("userphone");
                String idCard= resultSet.getString("idCard");
                Timestamp register_time = resultSet.getTimestamp("register_time");
                Timestamp latest_time = resultSet.getTimestamp("latest_time");

                Courier courier = new Courier(id, username, password,
                        userphone, sendNum, idCard, register_time, latest_time);

                return courier;
            }


        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }

        return null;
    }

    @Override
    public List<Courier> findAll(boolean limit, int offset, int pageNum) {
        List<Courier >  data=new ArrayList<>();
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            if(limit){
                preparedStatement = connection.prepareStatement(SQL_FIND_LIMIT);
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNum);


            }else{
                preparedStatement=connection.prepareStatement(SQL_FIND_ALL);
            }

            resultSet= preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");

                String password = resultSet.getString("password");
                String username = resultSet.getString("username");
                int sendNum = resultSet.getInt("sendNum");
                String userphone= resultSet.getString("userphone");
                String idCard= resultSet.getString("idCard");
                Timestamp register_time = resultSet.getTimestamp("register_time");
                Timestamp latest_time = resultSet.getTimestamp("latest_time");


                Courier courier = new Courier(id, username, password, idCard, sendNum, userphone, register_time, latest_time);
                data.add(courier);
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);

        }
        return data;

    }

    @Override
    public int getTotal() {
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        int allNum=0;
        try {
            preparedStatement = connection.prepareStatement(SQL_TOTAL);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                allNum= resultSet.getInt("total");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return allNum;
    }

    @Override
    public void updateLatestTime(String username, Date date) {
        Connection connection=DruidUtil.getConnection();
        PreparedStatement preparedStatement=null;

        try {
            preparedStatement = connection.prepareStatement(SQL_UPDATE_LATEST_TIME);
            preparedStatement.setDate(1, new java.sql.Date(date.getTime()));
            preparedStatement.setString(2, username);
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
    }


}
